import 'dart:io';

import 'package:flutter/foundation.dart';
import 'package:private_journey/States.dart';
import 'package:sqflite/sqflite.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';
import 'package:sqflite_common_ffi_web/sqflite_ffi_web.dart';

class KeyCodeSummary {
  KeyCodeInfo keyCodeInfo;
  int count;

  KeyCodeSummary(this.keyCodeInfo, this.count);

  @override
  String toString() {
    return 'KeyCodeSummary{key: $keyCodeInfo, count: $count}';
  }
}

Future<List<KeyCodeSummary>> getUserKeysConfig() async {
  var db = await DbService().database;
  var result = await db.rawQuery('select * from user_keys_config ');
  List<KeyCodeSummary> list = [];
  for (var element in result) {
    list.add(
      KeyCodeSummary(
        KeyCodeInfo(
          element['keyboard_key_text'] as String,
          element['keyboard_code'] as int,
        ),
        element['key_count'] as int,
      ),
    );
  }
  return list;
}

Future<void> updateUserKeysConfig(KeyCodeInfo s, {int keyTotal = 1}) async {
  if (s.key == '') {
    return;
  }
  var db = await DbService().database;
  var result = await db.rawQuery(
    'select * from user_keys_config where keyboard_code="${s.code}" ',
  );
  if (result.isNotEmpty) {
    await db.rawUpdate(
      'update user_keys_config set keyboard_key_text="${s.key}",key_count=${keyTotal} where keyboard_code="${s.code}" ',
    );
  } else {
    await db.rawInsert(
      'insert into user_keys_config (keyboard_key_text,keyboard_code) values ("${s.key}","${s.code}")',
    );
  }
}

class DbService {
  static final DbService _instance = DbService._();

  factory DbService() => _instance;

  DbService._();

  static Database? _database;

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await initDB();
    return _database!;
  }

  static initDB() async {
    if (Platform.isWindows) {
      databaseFactory = databaseFactoryFfi;
    }
    if (!kIsWeb) {
      return await openDatabase(r'C:\home\site_hub.db', version: 1);
    }
    if (kIsWeb) {
      var facory = databaseFactoryFfiWeb;
      return await facory.openDatabase('history.db');
    }
  }

  static void init() async {
    await _initMySitesTable();
    await _initUserSettingsTable();
    await _initUserKeysConfigTable();
  }

  static _initUserKeysConfigTable() async {
    var db = await _instance.database;
    var table = await db.rawQuery(
      'select * from sqlite_master where type="table" and name="user_keys_config"',
    );
    if (table.isEmpty) {
      await db.execute(r''' 
create table user_keys_config
(
    id                integer not null
        constraint user_keys_config_pk primary key autoincrement,
    keyboard_key_text text    not null,
    keyboard_code     integer not null,
    key_count        integer not null default 1
)
''');
    }
  }

  static _initUserSettingsTable() async {
    var db = await _instance.database;
    var table = await db.rawQuery(
      'select * from sqlite_master where type="table" and name="user_settings"',
    );
    if (table.isEmpty) {
      await db.execute(r'''
create table user_settings
(
    id    integer not null
        constraint user_settings_pk primary key autoincrement,
    key   text    not null,
    value text    not null
)
        ''');
    }
  }

  static _initMySitesTable() async {
    var db = await _instance.database;
    var table = await db.rawQuery(
      'select * from sqlite_master where type="table" and name="my_sites"',
    );
    if (table.isEmpty) {
      await db.execute(r'''
        CREATE TABLE my_sites
(
    id        integer not null
        constraint my_sites_pk
            primary key autoincrement,
    site_name text    not null,
    site_url  text    not null,
    icon_url  text    not null,
    unique (id)
)
        ''');
    }
  }

  Future<void> updateUserBackGroundImage(String url) async {
    var db = await _instance.database;
    var data = await db.rawQuery('SELECT * FROM user_settings WHERE key = ?', [
      'user_back_ground_image',
    ]);
    if (data.isEmpty) {
      await db.insert('user_settings', {
        'key': 'user_back_ground_image',
        'value': url,
      });
    } else {
      await db.update(
        'user_settings',
        {'value': url},
        where: 'key=?',
        whereArgs: ['user_back_ground_image'],
      );
    }
  }

  Future<String> getUserBackGroundImage() async {
    var db = await _instance.database;
    var data = await db.rawQuery('SELECT * FROM user_settings WHERE key =?', [
      'user_back_ground_image',
    ]);
    if (data.isEmpty) {
      return '';
    }
    return data[0]['value'] as String;
  }
}
